﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DotNet.Configuration;
using DotNet.Data;
using DotNet.Extensions;
using DotNet.Helper;
using XNWebModel;

namespace XNWebService
{
    /// <summary>
    /// Api服务类
    /// </summary>
    public static class ApiService
    {
        private static readonly Dictionary<string, KeyValuePair<double, double>> NodeLongLatDic =
            new Dictionary<string, KeyValuePair<double, double>>();

        private static readonly Dictionary<string, string> RegionDic = new Dictionary<string, string>();

        static ApiService()
        {
            var db = new ApiDatabase();
            var sql = "select stationid, newlongitude, newlatitude from hxzdjwd  where oldlongitude>0";
            var dt = db.ExecuteDataTable(sql);
            foreach (DataRow row in dt.Rows)
            {
                var stationid = row["stationid"].ToString();
                var newlongitude = row["newlongitude"].ToDouble();
                var newlatitude = row["newlatitude"].ToDouble();
                NodeLongLatDic.Add(stationid, new KeyValuePair<double, double>(newlongitude, newlatitude));
            }
        }

        #region Api

        /// <summary>
        /// 根据公交上下行线路标识获取线路走向数据
        /// </summary>
        /// <param name="segmentId">公交上下行线路标识</param>
        public static XLJWInfoModel GetXLJW(string segmentId)
        {
            const string sql = @"select routeid,segmentid,newxljw xljw from hxxljwd 
                                    where segmentid=@segmentid";
            var db = new ApiDatabase();
            return db.Query<XLJWInfoModel>(sql, new object[] { segmentId }).FirstOrDefault() ?? new XLJWInfoModel();
        }

        /// <summary>
        /// 根据公交上下行线路标识获取公交站线路途经站点数据
        /// </summary>
        /// <param name="segmentId">公交上下行线路标识</param>
        public static WayInfoModel GetWay(string segmentId)
        {
            const string sql = @"select b.segmentid,a.routeid,a.routename from ods.t_61_xlxx a join (
                                    select segmentid, routeid from ods.t_61_dcxlxx where segmentid = @segmentid) b
                                    on a.routeid = b.routeid and a.isactive=1";
            var db = new ApiDatabase();
            var wayModel = db.Query<WayInfoModel>(sql, new object[] { segmentId }).FirstOrDefault() ?? new WayInfoModel();
            wayModel.STATIONS = GetWayStationModelList(segmentId.ToInt());
            return wayModel;
        }

        /// <summary>
        /// 通过线路ID查询线路信息
        /// </summary>
        /// <param name="routeId">线路ID</param>
        public static LineInfoModel GetLine(string routeId)
        {
            //1.获取线路基础信息
            var lineInfoModel = GetLineInfoModel(routeId) ?? new LineInfoModel();
            //2.获取上下行线路
            lineInfoModel.SEGMENTS = GetDCLineModelList(routeId);
            return lineInfoModel;
        }

        /// <summary>
        /// 通过公交站点名称模糊查询站点信息
        /// </summary>
        /// <param name="stationName">站点名称</param>
        /// <param name="isFuzMatching">true 模糊匹配,false 精确查找</param>
        public static List<StationInfoModel> GetStation(string stationName, bool isFuzMatching)
        {
            if (string.IsNullOrEmpty(stationName))
            {
                return new List<StationInfoModel>();
            }
            //获取站点基础信息
            var stationInfoModelList = GetStationInfoModelList(stationName, isFuzMatching);
            foreach (var model in stationInfoModelList)
            {
                //获取站点线路信息
                model.BUSLINE = GetWayLineModelList(model.STATIONID);
                model.BUSLINE.OrderByAsc(p => p.ROUTEID);
            }
            return stationInfoModelList;
        }

        /// <summary>
        /// 通过公交站点名称模糊查询站点信息
        /// </summary>
        /// <param name="stationName">站点名称</param>
        /// <param name="isFuzMatching">true 模糊匹配,false 精确查找</param>
        public static List<StationInfoModel> GetMergeStation(string stationName, bool isFuzMatching)
        {
            if (string.IsNullOrEmpty(stationName))
            {
                return new List<StationInfoModel>();
            }
            //获取站点基础信息
            var stationInfoModelList = GetStationInfoModelList(stationName, isFuzMatching);
            foreach (var model in stationInfoModelList)
            {
                //获取站点线路信息
                model.BUSLINE = GetWayLineModelList(model.STATIONID);
                //model.BUSLINE.OrderByAsc(p => p.ROUTEID);
            }
            var mapName = new Dictionary<string, StationInfoModel>();
            var mapList = new Dictionary<string, List<WayLineModel>>();
            foreach (var item in stationInfoModelList)
            {
                var key = item.STATIONNAME.Trim();
                if (!mapName.ContainsKey(key))
                {
                    mapName.Add(key, item);
                    mapList.Add(key, new List<WayLineModel>());
                }
                var slist = mapList[key];
                foreach (var m in item.BUSLINE)
                {
                    if (slist.All(p => p.ROUTEID != m.ROUTEID))
                    {
                        slist.Add(m);
                    }
                }
            }
            stationInfoModelList = new List<StationInfoModel>();
            foreach (KeyValuePair<string, StationInfoModel> item in mapName)
            {
                item.Value.BUSLINE = mapList[item.Key];
                item.Value.BUSLINE.OrderByAsc(p => p.ROUTEID);
                stationInfoModelList.Add(item.Value);
            }
            return stationInfoModelList;
        }

        /// <summary>
        /// 通过公交站点Id查询站点信息
        /// </summary>
        /// <param name="stationId">站点Id</param>
        public static StationInfoModel GetStationById(string stationId)
        {
            //获取站点基础信息
            var stationInfoModel = GetStationInfoModelById(stationId);

            //获取站点线路信息
            stationInfoModel.BUSLINE = GetWayLineModelList(stationInfoModel.STATIONID);

            return stationInfoModel;
        }

        /// <summary>
        /// 通过线路名称查询对应的线路号信息
        /// </summary>
        /// <param name="buslinename">线路名称</param>
        public static List<BaseLineModel> GetBusLineByName(string buslinename)
        {
            const string sql = @"select routeid,routename from ods.t_61_xlxx where isactive=1 and routename like @routename
                                    order by routeid asc";
            var db = new ApiDatabase();
            return db.Query<BaseLineModel>(sql, new object[] { "%" + buslinename + "%" }).ToList();
        }

        /// <summary>
        /// 根据公交上下行线路标识获取公交单程线路中的首末班时间和（起步）票价
        /// </summary>
        /// <param name="segmentId">公交上下行线路标识</param>
        public static BusLineInfoModel GetBusLineInfoBySegmentId(string segmentId)
        {
            const string sql = @"select fstsendtime,(select TICKET normalvalue from ods.t_61_xlxx t where t.routeid = y.routeid  ) normalvalue,lstsendtime 
                                    from ods.t_61_dcxlxx y 
                                    where segmentid=@segmentid";
            var db = new ApiDatabase();
            return db.Query<BusLineInfoModel>(sql, new object[] { segmentId }).FirstOrDefault() ?? new BusLineInfoModel();
        }

        /// <summary>
        /// 通过线路标识查询实时公交信息
        /// </summary>
        /// <param name="routeid">线路ID</param>
        public static RealBusModel GetRealBusLine(string routeid)
        {
            var realBusModel = new RealBusModel();
            realBusModel.message = "success";
            realBusModel.interval = ConfigManager.GetAppValue("BusRefrshInterval", () => "10").ToInt();

            var dCLineModelList = GetDCLineModelList(routeid);
            BusLineInfoModel upLineInfoModel = null;
            BusLineInfoModel downLineInfoModel = null;
            //上行
            if (dCLineModelList.Count > 0)
            {
                realBusModel.up = GetRealBusStationModelList(dCLineModelList[0], true);
                upLineInfoModel = GetBusLineInfoBySegmentId(dCLineModelList[0].SEGMENTID.ToString());
            }
            //下行
            if (dCLineModelList.Count > 1)
            {
                realBusModel.down = GetRealBusStationModelList(dCLineModelList[1], false);
                downLineInfoModel = GetBusLineInfoBySegmentId(dCLineModelList[1].SEGMENTID.ToString());
            }

            realBusModel.info = new
            {
                up = new RealBusLineInfoModel(upLineInfoModel),
                down = new RealBusLineInfoModel(downLineInfoModel)
            };

            return realBusModel;
        }

        #endregion

        #region private 实时公交

        private static List<RealBusStationModel> GetRealBusStationModelList(DCLineModel dCLineModel, bool isup)
        {
            var result = new List<RealBusStationModel>();
            var dzrealDZModelList = GetRealDZModelList(dCLineModel, isup, 1);
            var lzrealDZModelList = GetRealDZModelList(dCLineModel, isup, 2);
            var up_station_list = dCLineModel.STATIONS;
            foreach (var item in up_station_list)
            {
                result.Add(new RealBusStationModel
                {
                    station_id = item.STATIONID,
                    station_name = item.STATIONNAME,
                    running_type = dCLineModel.SEGMENTID,
                    order_number = item.DUALSERIALID,
                    bus_codes = GetBusCodes(item.STATIONID, dzrealDZModelList),
                    leavebus_codes = GetBusCodes(item.STATIONID, lzrealDZModelList)
                });
            }
            return result;
        }

        private static string Join<T>(T[] arr, string split = ",")
        {
            StringBuilder sb = new StringBuilder(arr.Length * 36);
            for (int i = 0; i < arr.Length; i++)
            {
                sb.Append(arr[i].ToString());
                if (i < arr.Length - 1)
                {
                    sb.Append(split);
                }
            }
            return sb.ToString();
        }

        //private static List<string> GetRealBusLineAllBusSelfIdList(int routeId, int segmentId)
        //{
        //    var sdate = DateTimeHelper.FormatDate(DateTime.Now);
        //    var edate = DateTimeHelper.FormatDate(DateTime.Now.AddDays(1));
        //    string sql = $@"select distinct busselfid from ods.t_61_clfcjl 
        //                    where routeid=@routeId and segmentid=@segmentId and 
        //                    leavetime between to_date('{sdate}','yyyy-mm-dd') and to_date('{edate}','yyyy-mm-dd')";
        //    var db = new ApiDatabase();
        //    return db.Query<string>(sql, new object[] { routeId, segmentId }).ToList();
        //}

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dCLineModel"></param>
        /// <param name="isup"></param>
        /// <param name="dznum">//到站标识:1.到站 2.离站</param>
        /// <returns></returns>
        private static List<RealDZModel> GetRealDZModelList(DCLineModel dCLineModel, bool isup, int dznum)
        {
            /*
             select t.CARNUM,t.FX,t.DZNUM,t.sj,k.ROUTEID,k.DUALSERIALID,k.STATIONID,m.stationname from daolizhan t, T_61_XLZDDYB k,
    t_61_zdxx m where t.XLNUM='1'
    and t.sj > to_date('2016-10-26','yyyy-mm-dd hh24:mi:ss')
    and t.sj < to_date('2016-10-27','yyyy-mm-dd hh24:mi:ss')
    and t.XLNUM=k.ROUTEID and t.zdnum=k.DUALSERIALID and m.STATIONID=k.STATIONID order by m.STATIONNAME
             */
            //return new List<RealDZModel>();
            int routeId = dCLineModel.ROUTEID.ToInt();
            //int segmentId = dCLineModel.SEGMENTID;
            //var busSelfIds = GetRealBusLineAllBusSelfIdList(routeId, segmentId);
            //if (busSelfIds.Count == 0)
            //{
            //    return new List<RealDZModel>();
            //}
            //var fx = isup ? 4 : 5;// and t.fx={fx}
            string sql = $@"select t.CARNUM busselfid,k.stationid from daolizhan t, T_61_XLZDDYB k,
                t_61_zdxx m where t.XLNUM={routeId} and dznum={dznum}
                and t.sj > to_date('{DateTimeHelper.FormatDate(DateTime.Now)}','yyyy-mm-dd')
                and t.sj < to_date('{DateTimeHelper.FormatDate(DateTime.Now.AddDays(1))}','yyyy-mm-dd')
                and t.XLNUM=k.ROUTEID and t.zdnum=k.DUALSERIALID and m.STATIONID=k.STATIONID";

            //select busselfid,stationindex from daolizhan 
            //        where createtime = to_date('{DateTimeHelper.FormatDate(DateTime.Now)}','yyyy-mm-dd') 
            //            and cstype={cstype} and busselfid in ({StringHelper.ConvertArrayToString(busSelfIds, ",", "'")})";
            var db = new ApiDatabase();
            var result = db.Query<RealDZModel>(sql).ToList();
            //foreach (var item in result)
            //{
            //    var ys = item.stationindex % dCLineModel.STATIONNUM;
            //    var n = dCLineModel.STATIONS.Skip(ys - 1).Take(1).FirstOrDefault() ?? new WayStationModel();
            //    item.stationid = n.STATIONID;
            //}
            return result;
        }

        private static string GetBusCodes(string currentStationId, List<RealDZModel> realDZModelList)
        {
            var list = realDZModelList.Where(p => p.stationid == currentStationId).Select(p => p.busselfid).ToList();
            if (list.Count == 0)
            {
                return string.Empty;
            }

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < list.Count; i++)
            {
                sb.Append($"{list[i]},1");
                if (i < list.Count - 1)
                {
                    sb.Append("_");
                }
            }
            return sb.ToString();
        }

        #endregion

        #region private 通过公交站点名称模糊查询站点信息

        /// <summary>
        /// 
        /// </summary>
        /// <param name="stationName"></param>
        /// <param name="isFuzMatching">true 模糊匹配,false 精确查找</param>
        /// <returns></returns>
        private static List<StationInfoModel> GetStationInfoModelList(string stationName, bool isFuzMatching)
        {
            string sql = string.Empty;
            var db = new ApiDatabase();
            List<StationInfoModel> list = null;
            if (isFuzMatching) //模糊匹配
            {
                sql = @"select roadname,stationid,longitude,stationname,latitude from ods.t_61_zdxx where flag=1 and stationname like @stationname";
                list = db.Query<StationInfoModel>(sql, new object[] { "%" + stationName + "%" }).ToList();
            }
            else //精确匹配
            {
                sql = @"select roadname,stationid,longitude,stationname,latitude from ods.t_61_zdxx where flag=1 and stationname = @stationname";
                list = db.Query<StationInfoModel>(sql, new object[] { stationName }).ToList();
            }

            foreach (var model in list)
            {
                if (NodeLongLatDic.ContainsKey(model.STATIONID))
                {
                    var kv = NodeLongLatDic[model.STATIONID];
                    model.LONGITUDE = kv.Key;
                    model.LATITUDE = kv.Value;
                }
            }
            return list;
        }

        private static StationInfoModel GetStationInfoModelById(string stationId)
        {
            const string sql = @"select roadname,stationid,longitude,stationname,latitude from ods.t_61_zdxx where 
                                    stationid=@stationid";
            var db = new ApiDatabase();
            var model = db.Query<StationInfoModel>(sql, new object[] { stationId }).FirstOrDefault() ?? new StationInfoModel();
            if (NodeLongLatDic.ContainsKey(model.STATIONID))
            {
                var kv = NodeLongLatDic[model.STATIONID];
                model.LONGITUDE = kv.Key;
                model.LATITUDE = kv.Value;
            }
            return model;
        }


        private static List<WayLineModel> GetWayLineModelList(string stationId)
        {
            const string sql = @"select routeid,( select x.routename from ods.t_61_xlxx x where x.routeid=y.routeid ) routename,
                                    stationid,dualserialid from ods.t_61_xlzddyb y where stationid=@stationid";
            var db = new ApiDatabase();
            var wayLineModelList = db.Query<WayLineModel>(sql, new object[] { stationId }).ToList();
            //设置上下行ID
            SetWayLineModelListSegmentId(wayLineModelList, stationId);
            return wayLineModelList;
        }

        private static void SetWayLineModelListSegmentId(List<WayLineModel> wayLineModelList, string stationId)
        {
            foreach (var model in wayLineModelList)//循环所有线路
            {
                var dCLineModelList = GetDCLineModelList(model.ROUTEID.ToString());//获取当前线路的上下行线路
                foreach (var item in dCLineModelList)
                {
                    var stationList = item.STATIONS;//取出当前上下行线路中的所有站点
                    if (stationList.Any(p => p.STATIONID == stationId))
                    {
                        //如果有这个站点 则标识站点的上下行ID,由于站点是唯一的,所以退出循环
                        model.SEGMENTID = item.SEGMENTID;
                        break;
                    }
                }
            }
        }

        #endregion

        #region private 通过线路ID查询线路信息

        private static LineInfoModel GetLineInfoModel(string routeId)
        {
            const string sql = @"select routename,TICKET normalvalue from ods.t_61_xlxx where isactive=1 and routeid=@routeid";
            var db = new ApiDatabase();
            return db.Query<LineInfoModel>(sql, new object[] { routeId }).FirstOrDefault() ?? new LineInfoModel();
        }

        private static List<DCLineModel> GetDCLineModelList(string routeId)
        {
            const string sql = @"select fstsendtime,lstsendtime,
                fststationid,( select stationname from ods.t_61_zdxx where stationid=fststationid  ) fststationname,
                lststationid,( select stationname from ods.t_61_zdxx where stationid=lststationid  ) lststationname,
                routeid,stationnum,segmentid,vernum
                from ods.t_61_dcxlxx
                where isactive=1 and flag=1 and routeid=@routeid
                order by rundirection asc";
            var db = new ApiDatabase();
            var dCLineModelList = db.Query<DCLineModel>(sql, new object[] { routeId }).ToList();
            var map = new Dictionary<int, int>();
            foreach (var model in dCLineModelList)
            {
                if (map.ContainsKey(model.SEGMENTID))
                {
                    var v = map[model.SEGMENTID];
                    if (model.VERNUM > v)
                    {
                        map[model.SEGMENTID] = model.VERNUM;
                    }
                }
                else
                {
                    map[model.SEGMENTID] = model.VERNUM;
                }
            }

            foreach (var item in map)
            {
                dCLineModelList.Delete(p => p.SEGMENTID == item.Key && p.VERNUM != item.Value);
            }

            foreach (var model in dCLineModelList)
            {
                //获取上下行线路站点信息
                model.STATIONS = GetWayStationModelList(model.SEGMENTID);
            }
            return dCLineModelList;
        }

        #endregion

        #region private 根据公交上下行线路标识获取当前方向站点列表

        /// <summary>
        /// 根据公交上下行线路标识获取当前方向站点列表
        /// </summary>
        /// <param name="segmentId">公交上下行线路标识</param>
        private static List<WayStationModel> GetWayStationModelList(int segmentId)
        {
            //1.获取当前线路方向下的线路Id,起始站,终点站
            var stationStartEndModel = GetStationStartEndModel(segmentId);
            if (stationStartEndModel == null)
            {
                return new List<WayStationModel>();
            }
            //2.获取到当前线路下对应的所有站点信息
            var resultWayStationModelList = GetWayStationModelListCore(segmentId, stationStartEndModel);
            return resultWayStationModelList;
        }

        /// <summary>
        /// 获取当前线路方向下的线路Id,起始站,终点站
        /// </summary>
        /// <param name="segmentId">公交上下行线路标识</param>
        private static StationStartEndModel GetStationStartEndModel(int segmentId)
        {
            //先查询到当前线路方向下的 线路Id,起始站,终点站
            const string sql = @"select routeid,fststationid,lststationid from ods.t_61_dcxlxx where segmentid=@segmentid";
            var db = new ApiDatabase();
            var stationStartEndModel = db.Query<StationStartEndModel>(sql,
                new object[] { segmentId }).FirstOrDefault();
            return stationStartEndModel;
        }

        /// <summary>
        /// 获取到当前线路下对应的所有站点信息
        /// </summary>
        /// <param name="segmentId">公交上下行线路标识</param>
        /// <param name="stationStartEndModel">当前线路方向下的线路Id,起始站,终点站</param>
        private static List<WayStationModel> GetWayStationModelListCore(int segmentId,
            StationStartEndModel stationStartEndModel)
        {
            var resultWayStationModelList = new List<WayStationModel>();
            //获取到当前线路下对应的所有站点信息
            const string sql = @"select a.routeid,a.stationid,a.dualserialid,b.stationname,b.longitude,b.latitude from ods.t_61_xlzddyb a join t_61_zdxx b
                    on a.stationid=b.stationid
                    where routeid=@routeid and b.flag=1
                    order by dualserialid asc";
            var db = new ApiDatabase();
            var wayStationModelList = db.Query<WayStationModel>(sql, new object[] { stationStartEndModel.ROUTEID }).ToList();

            var isAdd = false;
            foreach (var model in wayStationModelList)
            {
                if (NodeLongLatDic.ContainsKey(model.STATIONID))
                {
                    var kv = NodeLongLatDic[model.STATIONID];
                    model.LONGITUDE = kv.Key;
                    model.LATITUDE = kv.Value;
                }


                //如果站点等于起始站,那么就标识开始添加站点
                if (model.STATIONID == stationStartEndModel.FSTSTATIONID)
                {
                    isAdd = true;
                }
                //如果站点等于终点站,那么就添加当前站点,并终止循环
                if (model.STATIONID == stationStartEndModel.LSTSTATIONID)
                {
                    resultWayStationModelList.Add(model);
                    break;
                }

                if (isAdd)
                {
                    resultWayStationModelList.Add(model);
                }
            }

            return resultWayStationModelList;
        }

        #endregion

        /// <summary>
        /// 根据坐标和公交线路标识(上下行标识)获取该线路距离自己最近的站点信息
        /// </summary>
        /// <param name="segmentId">公交上下行线路标识</param>
        /// <param name="longitude">经度</param>
        /// <param name="latitude">纬度</param>
        public static List<WayStationDistanceModel> GetNearBusStationByCordLineId(string segmentId,
            string longitude, string latitude)
        {
            var nearBusCount = ConfigManager.GetAppValue("NearBusCount", () => "1").ToInt();
            List<WayStationModel> nodeList = GetWayStationModelList(segmentId.ToInt());
            var targetList = new List<WayStationDistanceModel>();
            foreach (var item in nodeList)
            {
                targetList.Add(new WayStationDistanceModel
                {
                    STATIONID = item.STATIONID,
                    STATIONNAME = item.STATIONNAME,
                    LONGITUDE = item.LONGITUDE,
                    LATITUDE = item.LATITUDE,
                    DUALSERIALID = item.DUALSERIALID,
                    Distance = Distance.TwoLonLatDistance(item.LATITUDE, item.LONGITUDE, latitude.ToDouble(), longitude.ToDouble())
                });
            }
            return targetList.OrderByAsc(p => p.Distance).Take(nearBusCount).ToList();
        }

        public static object GetNearparkingByDistance(string distance, string longitude, string latitude)
        {
            try
            {
                var sql = "select prkingid, prkingname Pltname, roadname, coordinatex, coordinatey from ods.t_71_parking_lot";
                var db = new ApiDatabase();
                var parkingLotList = db.Query<ParkingLotModel>(sql).ToList();
                foreach (var item in parkingLotList)
                {
                    item.distance = Distance.TwoLonLatDistance(item.coordinatey.ToDouble(), item.coordinatex.ToDouble(), latitude.ToDouble(),
                        longitude.ToDouble()).ToString("0.00").ToDouble() * 1000;
                }
                var newlist = parkingLotList.Where(p => p.distance <= distance.ToDouble()).ToList().OrderByAsc(p => p.distance).ToList();
                return new
                {
                    succ = "1",
                    count = newlist.Count,
                    msg = string.Empty,
                    data = newlist
                };
            }
            catch (Exception ex)
            {
                return new
                {
                    succ = "0",
                    count = 0,
                    msg = ex.Message.ReplaceEnter()
                };
            }
        }

        /// <summary>
        /// 获取最新的西宁市的交通指数数据
        /// </summary>
        /// <param name="regiontype">
        /// 行政区划代码：630104---城西\630102---城东\630103---城中\630105---城北\630100---西宁市
        /// </param>
        public static object GetTrafficIndexData(string regiontype)
        {
            if (string.IsNullOrEmpty(regiontype))
            {
                return new
                {
                    succ = "0",
                    count = 0,
                    msg = "请指定行政区划代码"
                };
            }
            var sql = $@"select * from (
            select rownum, daydate, datatime, conditionindex, avgspeed, regiontype from ods.v_41_fsjtzs
              where daydate = '{DateTimeHelper.FormatDate(DateTime.Now, "yyyyMMdd")}' and regiontype = @regiontype
            order by datatime desc
            ) where rownum = 1
            ";
            //            var sql = $@"select * from (
            //select rownum, daydate, datatime, conditionindex, avgspeed, regiontype from ods.t_41_fsjtzs
            //  where daydate = '20170102' and regiontype = @regiontype
            //order by datatime desc
            //) where rownum = 1
            //";
            var db = new ApiDatabase();
            var list = db.Query<TrafficIndexDataModel>(sql, new object[] { regiontype }).ToList();
            foreach (var item in list)
            {
                item.daydate = DateTimeHelper.FormatDate(item.daydate.ToDateTime(), "yyyy-MM-dd");
                item.datatime = DateTimeHelper.FormatDate(item.datatime.ToDateTime(), "HH:ss");
            }
            return new
            {
                succ = "1",
                count = list.Count,
                msg = string.Empty,
                data = list
            };
        }

        /// <summary>
        /// 获取最新的分区域交通指数信息,返回结果以行政区划（regiontype）由小到大排序
        /// </summary>
        public static object GetRegionalTransportationIndex()
        {
            if (RegionDic.Count == 0)
            {
                RegionDic["630100"] = "西宁市";
                RegionDic["630102"] = "城东";
                RegionDic["630103"] = "城中";
                RegionDic["630104"] = "城西";
                RegionDic["630105"] = "城北";
            }

            var sql = $@"select daydate, datatime, conditionindex, avgspeed, regiontype
  from (select a.*,row_number() over(partition by regiontype order by datatime desc) rw
          from ods.v_41_fsjtzs a
         where a.daydate = '{DateTimeHelper.FormatDate(DateTime.Now, "yyyyMMdd")}') t
 where t.rw = 1
 order by regiontype asc";
            var db = new ApiDatabase();
            var list = db.Query<TransportationIndexModel>(sql).ToList();
            foreach (var item in list)
            {
                item.daydate = DateTimeHelper.FormatDate(item.daydate.ToDateTime(), "yyyy-MM-dd");
                item.datatime = DateTimeHelper.FormatDate(item.datatime.ToDateTime(), "HH:ss");
                if (RegionDic.ContainsKey(item.regiontype))
                {
                    item.regiontype = RegionDic[item.regiontype];
                }
            }
            return new
            {
                succ = "1",
                count = list.Count,
                msg = string.Empty,
                data = list
            };
        }

        public static object GetMainRoadTravelSpeed(string rlevel)
        {
            var sql = $@"select daydate,dtimes,currspeed,prehourspeed,preweekspeed,routename,rlevel 
from (select a.*,row_number() over(partition by routename order by dtimes desc) rw
          from ods.t_41_roadspeed a
          where a.daydate='{DateTimeHelper.FormatDate(DateTime.Now, "yyyyMMdd")}' and rlevel=@rlevel
           ) t
 where t.rw = 1";
            var db = new ApiDatabase();
            var list = db.Query<MainRoadTravelSpeedModel>(sql, new object[] { rlevel }).ToList();
            foreach (var item in list)
            {
                item.daydate = DateTimeHelper.FormatDate(item.daydate.ToDateTime(), "yyyy-MM-dd");
                item.dtimes = DateTimeHelper.FormatDate(item.dtimes.ToDateTime(), "HH:ss");
            }
            return new
            {
                succ = "1",
                count = list.Count,
                msg = string.Empty,
                data = list
            };
        }

        public static object GetLastStatusNum()
        {
            var sql = @"
select * from (select a.*,row_number() over(partition by xzqh order by datatime desc) rw
          from ods.t_91_taxiemptyweight a ) t
 where t.rw = 1
 order by xzqh desc";
            var db = new ApiDatabase();
            var list = db.Query<LastStatusNumModel>(sql).ToList();
            return new
            {
                succ = "1",
                count = list.Count,
                msg = string.Empty,
                data = list
            };
        }


        public static object GetCongestionTopRoad()
        {
            var sql = @"select * from toccoriginal.t_lk_top10";
            var db = new XaxnDatabase();
            var list = db.Query<CongestionTopRoad>(sql).ToList();
            return new
            {
                succ = "1",
                count = list.Count,
                msg = string.Empty,
                data = list
            };
        }




    }
}
